package com.db.processror;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.csv.CSVReader;
import com.db.DBCreator;
import com.trade.TradeBean;

public class TradeProcessor {
	static Connection con = null;

	public static void insertTrade(TradeBean trade) throws Exception {
		String insertQuery = "insert into trades(Date,Stock,Action,\"Qty.\",\"Price#\",\"Trade Value\",\"Brokerage incl. taxes\",\"Order Ref.\",Settlement,Segment,\"DP Id - Client DP Id\",Exchange) values(?,?,?,?,?,?,?,?,?,?,?,?)";
		if (con == null || con.isClosed()) {
			con = DBCreator.getConnection();
		}
		PreparedStatement pstmt = con.prepareStatement(insertQuery);
		pstmt.setDate(1, trade.getDate());
		pstmt.setString(2, trade.getStock());
		pstmt.setString(3, trade.getAction());
		pstmt.setInt(4, trade.getQty());
		pstmt.setDouble(5, trade.getPrice());
		pstmt.setDouble(6, trade.getTradeValue());
		pstmt.setDouble(7, trade.getBrokerageincltaxes());
		pstmt.setString(8, trade.getOrderRef());
		pstmt.setString(9, trade.getSettlement());
		pstmt.setString(10, trade.getSegment());
		pstmt.setString(11, trade.getDPIdClientDPId());
		pstmt.setString(12, trade.getExchange());
		pstmt.execute();
		pstmt.close();

	}

	public List<TradeBean> getTrades() throws Exception {
		List<TradeBean> trades = new ArrayList<TradeBean>();
		if (con == null || con.isClosed()) {
			con = DBCreator.getConnection();
		}
		Statement s = con.createStatement();
		ResultSet mytrades = s.executeQuery("select * from trades");
		while (mytrades.next()) {
			TradeBean trade = new TradeBean(mytrades.getLong(1), mytrades
					.getDate(2), mytrades.getString(3), mytrades.getString(4),
					mytrades.getInt(5), mytrades.getDouble(6), mytrades
							.getDouble(7), mytrades.getDouble(8), mytrades
							.getString(9), mytrades.getString(10), mytrades
							.getString(11), mytrades.getString(12), mytrades
							.getString(13));
			trades.add(trade);
		}
		closeCon();
		return trades;
	}

	public List<TradeBean> getSellTrades() throws Exception {
		List<TradeBean> trades = new ArrayList<TradeBean>();
		if (con == null || con.isClosed()) {
			con = DBCreator.getConnection();
		}
		Statement s = con.createStatement();
		ResultSet mytrades = s
				.executeQuery("select * from trades t where action='Sell' and not exists(select 1 from reconciledtrades where t.tradeid=sellTradeId group by sellTradeId having sum(buyQty)>=t.\"Qty.\") order by stock, date");
		while (mytrades.next()) {
			TradeBean trade = new TradeBean(mytrades.getLong(1), mytrades
					.getDate(2), mytrades.getString(3), mytrades.getString(4),
					mytrades.getInt(5), mytrades.getDouble(6), mytrades
							.getDouble(7), mytrades.getDouble(8), mytrades
							.getString(9), mytrades.getString(10), mytrades
							.getString(11), mytrades.getString(12), mytrades
							.getString(13));
			trades.add(trade);
		}
		closeCon();
		return trades;
	}

	public static void closeCon() {
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public void dropTable() throws Exception {
		if (con.isClosed()) {
			con = DBCreator.getConnection();
		}
		Statement s = con.createStatement();
		s.execute("drop table trades");
		s.execute(DBCreator.createTradeTableString);
		closeCon();
	}

	public void createReconcileTable() {
		Statement s = null;
		try {
			if (con.isClosed()) {
				con = DBCreator.getConnection();
			}
			s = con.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		FileWriter fw = null;
		try {
			String sql = "select * from reconciledtrades";
			PreparedStatement pstmt = con.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = pstmt.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			SimpleDateFormat sdf = new SimpleDateFormat("MMddyyyy-HHmmss");

			File f = new File(sdf.format(new Date()) + ".csv");
			fw = new FileWriter(f);
			fw.write("'");//needed for excel csv support only
						// refer http://support.microsoft.com/kb/323626
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				if (i == rsmd.getColumnCount()) {
					fw.write(rsmd.getColumnLabel(i));
					fw.write(System.getProperty("line.separator"));
					fw.flush();
				} else {
					fw.write(rsmd.getColumnLabel(i) + ",");
				}
			}
			rs.last();
			rs.beforeFirst();
			while (rs.next()) {
				for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
					if (i == rs.getMetaData().getColumnCount() - 1) {
						fw.write(rs.getObject(i + 1).toString());
						fw.write(System.getProperty("line.separator"));
						fw.flush();
					} else {
						fw.write(rs.getObject(i + 1).toString() + ",");
					}
				}
			}
			rs.close();
			pstmt.close();
			s.execute("drop table reconciledtrades");
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (fw != null) {
				try {
					fw.flush();
					fw.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		try {
			s.execute(DBCreator.createReconciledTradeTableString);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		closeCon();
	}
	
	public void generateReport() {
		
		try {
			if (con.isClosed()) {
				con = DBCreator.getConnection();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		FileWriter fw = null;
		try {
			String sql = "select buy.TRADEID,buy.DATE,buy.STOCK,buy.ACTION,r.buyqty,buy.\"Price#\",buy.\"Trade Value\",buy.\"Brokerage incl. taxes\",sell.TRADEID,sell.DATE,sell.STOCK,sell.ACTION,r.buyqty ,sell.\"Price#\",sell.\"Trade Value\"*r.buyqty/sell.\"Qty.\" as \"Trade Value\",sell.\"Brokerage incl. taxes\"*r.buyqty/sell.\"Qty.\" as \"Brokerage incl. taxes\" from reconciledtrades r,trades buy, trades sell where buy.tradeid=r.buytradeid and sell.tradeid=r.selltradeid";
			PreparedStatement pstmt = con.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = pstmt.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			SimpleDateFormat sdf = new SimpleDateFormat("MMddyyyy-HHmmss");

			File f = new File(sdf.format(new Date()) + "-reconciled.csv");
			fw = new FileWriter(f);
			fw.write("'");//needed for excel csv support only
						// refer http://support.microsoft.com/kb/323626
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				if (i == rsmd.getColumnCount()) {
					fw.write(rsmd.getColumnLabel(i));
					fw.write(System.getProperty("line.separator"));
					fw.flush();
				} else {
					fw.write(rsmd.getColumnLabel(i) + ",");
				}
			}
			rs.last();
			rs.beforeFirst();
			while (rs.next()) {
				for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
					if (i == rs.getMetaData().getColumnCount() - 1) {
						fw.write(rs.getObject(i + 1).toString());
						fw.write(System.getProperty("line.separator"));
						fw.flush();
					} else {
						fw.write(rs.getObject(i + 1).toString() + ",");
					}
				}
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (fw != null) {
				try {
					fw.flush();
					fw.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		closeCon();
	}

	public void loadTable() throws Exception {
		CSVReader csvr = new CSVReader();
		csvr.loadData();
	}

	public void loadTableFile(File f) throws Exception {
		CSVReader csvr = new CSVReader();
		csvr.loadData(f);
	}

	public void shutdownDB() {
		// Shutdown database
		try {
			DriverManager.getConnection("jdbc:derby:;shutdown=true");
		} catch (SQLException e) {
			if (!e.getSQLState().equals("XJ015")) {
				System.out.println("Database did not shut down normally");
				e.printStackTrace();
			}
		}
	}
}
